﻿using System;
using System.Collections;
using System.Configuration.Install;
using System.Data;
using System.Data.SqlClient;
using System.ServiceProcess;
using System.Xml;
using System.Threading;
using System.Collections.Generic;

namespace DotNetAgent.Core
{
	public class DatabaseHelper
	{
		public static int ExecuteSP(string SPName, string ConnStr)
		{
			return ExecuteSP(SPName, ConnStr, 600);
		}

		public static int ExecuteSP(string SPName, string ConnStr, int CommandTimeout)
		{
			SqlConnection conn = null;
			int Res;
			try
			{
				conn = new SqlConnection(ConnStr);
				conn.Open();
				SqlCommand Cmd = new SqlCommand(SPName, conn);
				Cmd.CommandType = CommandType.StoredProcedure;
				Cmd.CommandTimeout = CommandTimeout;
				Res = Cmd.ExecuteNonQuery();
			}
			catch (Exception ex)
			{
				if (conn != null)
				{
					conn.Close();
				}
				throw new AgentException("Error Executing SP (SP: " + SPName + ")(Error: " + ex.Message + ")", ex);
			}
			finally
			{
				if (conn != null)
				{
					conn.Close();
				}
			}
			return Res;
		}

		public static int ExecuteSQL(string SQL, string ConnStr)
		{
			return ExecuteSQL(SQL, ConnStr, 600);
		}

		public static int ExecuteSQL(string SQL, string ConnStr, int CommandTimeout)
		{
			SqlConnection conn = null;
			int Res;
			try
			{
				conn = new SqlConnection(ConnStr);
				conn.Open();
				SqlCommand Cmd = new SqlCommand(SQL, conn);
				Cmd.CommandTimeout = CommandTimeout;
				Cmd.CommandType = CommandType.Text;
				Res = Cmd.ExecuteNonQuery();
			}
			catch (Exception ex)
			{
				if (conn != null)
				{
					conn.Close();
				}
				throw new AgentException("Error Executing SQL (SQL: " + SQL + ")(Error: " + ex.Message + ")", ex);
			}
			finally
			{
				if (conn != null)
				{
					conn.Close();
				}
			}
			return Res;
		}

		public static void ExecuteSQL(string SQL, string ConnStr, ref DataTable dt)
		{
			SqlConnection conn = null;
			try
			{
				conn = new SqlConnection(ConnStr);
				conn.Open();
				new SqlDataAdapter(SQL, conn).Fill(dt);
			}
			catch (Exception ex)
			{
				if (conn != null)
				{
					conn.Close();
				}
				throw new AgentException("Error Executing SQL (SQL: " + SQL + ")(Error: " + ex.Message + ")", ex);
			}
			finally
			{
				if (conn != null)
				{
					conn.Close();
				}
			}
		}

		//public static void ExecuteSQLCE(string SQL, string ConnStr, ref DataTable dt)
		//{
		//    SqlCeConnection conn = null;
		//    try
		//    {
		//        conn = new SqlCeConnection(ConnStr);
		//        conn.Open();
		//        new SqlCeDataAdapter(SQL, conn).Fill(dt);
		//    }
		//    catch (Exception ex)
		//    {
		//        if (conn != null)
		//        {
		//            conn.Close();
		//        }
		//        throw new AgentException("Error Executing SQL (SQL: " + SQL + ")(Error: " + ex.Message + ")", ex);
		//    }
		//    finally
		//    {
		//        if (conn != null)
		//        {
		//            conn.Close();
		//        }
		//    }
		//}
	}
}